package dao.mysql;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import model.UserDTO;
import model.search.UserSearchDTO;
import dao.DAOException;
import dao.UserDAO;


public class UserDAOImpl extends BaseDAO implements UserDAO {
	public UserDAOImpl()
	{
		super();
	}

	private ArrayList<UserDTO> getList(PreparedStatement ps) throws SQLException {
		ArrayList<UserDTO> list = new ArrayList<UserDTO>();
		
		UserDTO data = null;
		
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			data = new UserDTO();
			data.setId(rs.getString("Id"));
			data.setName(rs.getString("Name"));
			data.setType(rs.getString("Type"));
			data.setEmail(rs.getString("Email"));
			data.setManagerId(rs.getString("ManagerId"));
			data.setManager(rs.getString("ManagerName"));
			data.setStatus(rs.getInt("Status"));
			list.add(data);
		}
		rs.close();
		
		return list;
	}
	
	public ArrayList<UserDTO> getAll() throws DAOException {
		openConnection(false);

		ArrayList<UserDTO> list = new ArrayList<UserDTO>();
		try {
			String sql = "Select u1.Id, u1.Name, u1.Type, u1.Email, IFNULL(u1.ManagerId, '-') AS ManagerId, IFNULL(u2.Name, '-') AS ManagerName, u1.Status " +
		                 "  From User u1 Left Join User u2 On u1.ManagerId = u2.Id ";
			PreparedStatement prep = this.getPreparedStatement(sql);
			list = getList(prep);
			prep.close();
		} catch (Exception e) {
			String error = "Error on Get All Users. Nested Exception is: "
					+ e;
			super.printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
		return list;
	}
	
	public UserDTO getUser(String userId) throws DAOException {
		openConnection(false);

		UserDTO data = null;

		try {
			String sql = "Select u1.Id, u1.Name, u1.Type, u1.Email, IFNULL(u1.ManagerId, '') AS ManagerId, IFNULL(u2.Name, '-') AS ManagerName, u1.Status " +
	                 "  From User u1 Left Join User u2 On u1.ManagerId = u2.Id " +
					 "  Where u1.Id = ? ";
			PreparedStatement preq = getPreparedStatement(sql);
			preq.setString(1, userId);
			ResultSet rs = preq.executeQuery();
			if (rs.next()) {
				data = new UserDTO();
				data.setId(rs.getString("Id"));
				data.setName(rs.getString("Name"));
				data.setType(rs.getString("Type"));
				data.setEmail(rs.getString("Email"));
				data.setManagerId(rs.getString("ManagerId"));
				data.setManager(rs.getString("ManagerName"));
				data.setStatus(rs.getInt("Status"));
			}

			rs.close();
			preq.close();
		} catch (Exception e) {
			String error = "Error on GetUser. Nested Exception is: " + e;
			super.printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}

		return data;
	}
	
	private String getWhereFilter(UserSearchDTO src) {
		String where = "";
		
		if (src.getId() != null) {
			where = " WHERE ";
			where += " u1.Id = ? ";
		}
		if (src.getPassword() != null) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " u1.Password = PASSWORD(?) ";
		}
		if (src.getName() != null && !src.getName().trim().isEmpty()) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " u1.Name LIKE ? ";
		}
		if (src.getType() != null && !src.getType().trim().isEmpty()) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " u1.Type = ? ";
		}
		if (src.getEmail() != null && !src.getType().trim().isEmpty()) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " u1.Email LIKE ? ";
		}
		if (src.getManager() != null && !src.getType().trim().isEmpty()) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			if ( src.getType().trim().equalsIgnoreCase("null") ) {
				where += " u1.ManagerId IS NULL ";
			} else {
				where += " u1.ManagerId LIKE ? ";
			}
		}
		if (src.getStatus() != null) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " u1.Status = ? ";
		}
		
		return where;
	}
	
	private ArrayList getParameters(UserSearchDTO src) {
		ArrayList params = new ArrayList();
		
		if (src.getId() != null) {
			params.add(src.getId());
		}
		if (src.getPassword() != null) {
			params.add(src.getPassword());
		}
		if (src.getName() != null && !src.getName().trim().isEmpty()) {
			params.add("%" + src.getName() + "%");
		}
		if (src.getType() != null && !src.getType().trim().isEmpty()) {
			params.add(src.getType());
		}
		if (src.getEmail() != null && !src.getType().trim().isEmpty()) {
			params.add("%" + src.getEmail() + "%");
		}
		if (src.getManager() != null && !src.getType().trim().isEmpty()) {
			if ( src.getType().trim().equalsIgnoreCase("null") ) {
			} else {
				params.add("%" + src.getManager() + "%");
			}
		}
		if (src.getStatus() != null) {
			params.add(src.getStatus());
		}
		
		return params;
	}
	
	public ArrayList<UserDTO> findUserByCriteria(UserSearchDTO src)
			throws DAOException {
		openConnection();

		ArrayList<UserDTO> list = new ArrayList<UserDTO>();
		try {
			String sql = "";
			String where = "";
			ArrayList params = new ArrayList();

			System.out.println(src);
			
			sql = "Select u1.Id, u1.Name, u1.Type, u1.Email, IFNULL(u1.ManagerId, '-') AS ManagerId, IFNULL(u2.Name, '-') AS ManagerName, u1.Status " +
	                 "  From User u1 Left Join User u2 On u1.ManagerId = u2.Id ";
			where = getWhereFilter(src);
			params = getParameters(src);
			
			PreparedStatement prep = getPreparedStatement(sql + " " + where);
			for (int i = 1; i <= params.size(); i++) {
				prep.setObject(i, params.get(i - 1));
			}

			printLog("Executing FindUserByCriteria: " + sql + where, Level.INFO);
			
			list = getList(prep);
			
			prep.close();
		} catch (Exception e) {
			String error = "Error FindUserByCriteria. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}

		return list;
	}
	
	@Override
	public void insertUser(UserDTO data) throws DAOException {
		openConnection(false);

		try {
			String sql = "INSERT INTO User " + 
		                 " ( Id, Name, Type, Password, Email, ManagerId, Status " +
						 " ) VALUES ( ?, ?, ?, Password(?), ?, ?, ? ) ";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, data.getId());
			prep.setString(2, data.getName());
			prep.setString(3, data.getType());
			prep.setString(4, data.getPassword());
			prep.setString(5, data.getEmail());
			prep.setObject(6, data.getManagerId().equals("") ? null : data.getManagerId());
			prep.setInt(7, data.getStatus());
			prep.executeUpdate();

			printLog("Executing Insert: " + sql, Level.INFO);

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Inserting User. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}

	@Override
	public void updateUser(UserDTO data) throws DAOException {
		openConnection(false);

		try {
			String sql = "UPDATE User SET Name = ?, Type = ?, "
					+ "Email = ?, ManagerId = ?, Status = ? WHERE (Id = ? ) ";			
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, data.getName()); 
			prep.setString(2, data.getType()); 
			prep.setString(3, data.getEmail()); 
			prep.setString(4, data.getManagerId().equals("") ? null : data.getManagerId()); 
			prep.setInt(5, data.getStatus()); 
			prep.setString(6, data.getId()); 
					
			prep.executeUpdate();

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Updating User. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}
	
	public void updatePassword(UserDTO data, String oldPassword) throws DAOException {
		openConnection(false);

		try {
			String sql = "UPDATE User SET Password = PASSWORD(?) "
					+ " WHERE (Id = ? ) AND ( Password = PASSWORD(?) ) ";			
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, data.getPassword()); 
			prep.setString(2, data.getId()); 
			prep.setString(3, oldPassword); 
					
			prep.executeUpdate();

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Updating Password. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}
	
	@Override
	public void deleteUser(UserDTO data) throws DAOException {
			openConnection(false);
		
		try {
			String sql = "DELETE FROM User WHERE (Id = ?)";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, data.getId());

			prep.executeUpdate();

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Deleting User. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}
}
